﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.IO;
using Excel;

namespace ECCSystem.Reports
{
    public partial class frmExtrasReport : Form
    {
        DataSet objDS = new DataSet();
        BizRule.ClsReports objBRReports;
        BizRule.ClsCommon objCommon;

        public frmExtrasReport()
        {
            InitializeComponent();
        }

        private void frmExtrasReport_Load(object sender, EventArgs e)
        {
            dtpFrom.Text = DateTime.Now.ToShortDateString();
            dtpTo.Text = DateTime.Now.ToShortDateString();
        }

        private void btnCreate_Click(object sender, EventArgs e)
        {
            if (this.CheckDate() == false)
            {
                MessageBox.Show("終了日付は開始日付より遅い。", "ECC Information");
                return;
            }
            btnCreate.Enabled = false;

            //open data source
            objBRReports = new ECCSystem.BizRule.ClsReports();
            objDS = (DataSet)objBRReports.GetExtrasReport(dtpFrom.Value.Date.ToString("yyyy/MM/dd"), dtpTo.Value.Date.ToString("yyyy/MM/dd"));
            if (objDS.Tables[0].Rows.Count <= 0)
            {
                MessageBox.Show("データ無し。", "ECC Information");
                btnCreate.Enabled = true;
                return;
            }

            //create report
            this.CreateExcel();

            btnCreate.Enabled = true;

        }

        //Check start date less than end date
        private bool CheckDate()
        {
            if (dtpFrom.Value > dtpTo.Value)
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        //excel operation(open,fill,quit)
        private void CreateExcel()
        {
            Excel.ApplicationClass objExlApp = new Excel.ApplicationClass();
            Excel.Workbook theWookbook = null;
            Excel.Worksheet theWookSheet = null;
            objCommon = new ECCSystem.BizRule.ClsCommon();
            try
            {
                objExlApp.Visible = false;

                object objMissing = System.Reflection.Missing.Value;
                StringBuilder strSavePath = new StringBuilder();
                strSavePath.Append("C:\\ECCReport\\");
                //if strSavePath is not existing,create the path
                if (Directory.Exists(strSavePath.ToString()) == false)
                {
                    Directory.CreateDirectory(strSavePath.ToString());
                }

                StringBuilder strTemplatePath = new StringBuilder();
                //Excel template path
                strTemplatePath.Append(System.Windows.Forms.Application.StartupPath);
                strTemplatePath.Append("\\ExtrasReport.xls");
                //if this template is existing or not
                if (File.Exists(strTemplatePath.ToString()) == false)
                {
                    MessageBox.Show("レポートのテンプレートファイルが見つかりません。", "ECC Information");
                    //quit excel object
                    objCommon.QuitExcelApp(objExlApp, theWookbook, theWookSheet, null);
                    System.GC.Collect();
                    btnCreate.Enabled = true;
                    return;
                }
                //open the excel template
                theWookbook = objExlApp.Workbooks.Open(strTemplatePath.ToString(), objMissing, objMissing, objMissing, objMissing, objMissing,
                                         objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing);
                //get the first sheet
                theWookSheet = (Excel.Worksheet)theWookbook.Worksheets[1];

                //fill data into excel
                theWookSheet.Cells[1, 1] = dtpFrom.Value.Date.ToString("yyyy/MM/dd") + "-" + dtpTo.Value.Date.ToString("yyyy/MM/dd");
                for (int i = 0; i < objDS.Tables[0].Rows.Count; i++)
                {
                    theWookSheet.Cells[i + 3, 2] = objDS.Tables[0].Rows[i][0].ToString();
                    theWookSheet.Cells[i + 4, 2] = objDS.Tables[0].Rows[i][1].ToString();
                    theWookSheet.Cells[i + 5, 2] = objDS.Tables[0].Rows[i][2].ToString();
                    theWookSheet.Cells[i + 6, 2] = objDS.Tables[0].Rows[i][3].ToString();
                    theWookSheet.Cells[i + 7, 2] = objDS.Tables[0].Rows[i][4].ToString();
                    theWookSheet.Cells[i + 8, 2] = objDS.Tables[0].Rows[i][5].ToString();
                    theWookSheet.Cells[i + 9, 2] = objDS.Tables[0].Rows[i][6].ToString();
                    theWookSheet.Cells[i + 10, 2] = objDS.Tables[0].Rows[i][7].ToString();
                    theWookSheet.Cells[i + 11, 2] = objDS.Tables[0].Rows[i][8].ToString();
                    theWookSheet.Cells[i + 12, 2] = objDS.Tables[0].Rows[i][9].ToString();
                    theWookSheet.Cells[i + 13, 2] = objDS.Tables[0].Rows[i][10].ToString();
                    theWookSheet.Cells[i + 14, 2] = objDS.Tables[0].Rows[i][11].ToString();
                    theWookSheet.Cells[i + 15, 2] = objDS.Tables[0].Rows[i][12].ToString();
                    theWookSheet.Cells[i + 16, 2] = objDS.Tables[0].Rows[i][13].ToString();
                    theWookSheet.Cells[i + 17, 2] = objDS.Tables[0].Rows[i][14].ToString();
                    theWookSheet.Cells[i + 18, 2] = objDS.Tables[0].Rows[i][15].ToString();
                    theWookSheet.Cells[i + 19, 2] = objDS.Tables[0].Rows[i][16].ToString();
                    theWookSheet.Cells[i + 20, 2] = objDS.Tables[0].Rows[i][17].ToString();
                    theWookSheet.Cells[i + 21, 2] = objDS.Tables[0].Rows[i][18].ToString();

                }

                //set excel report save path and name
                strSavePath.Append("ExtrasReport" + DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString());
                strSavePath.Append(DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString() + DateTime.Now.Second.ToString() + ".xls");

                //if the report has existed or not
                if (File.Exists(strSavePath.ToString()) == true)
                {
                    MessageBox.Show("このファイルがもう存在しました！", "ECC Information");
                    objCommon.QuitExcelApp(objExlApp, theWookbook, theWookSheet, null);
                    System.GC.Collect();
                    btnCreate.Enabled = true;
                    return;
                }

                //save the report
                theWookbook.SaveAs(strSavePath.ToString(), objMissing, objMissing, objMissing, objMissing, objMissing,
                    Excel.XlSaveAsAccessMode.xlNoChange, objMissing, objMissing, objMissing, objMissing);
                //prompt save success
                MessageBox.Show("操作成功しました。", "ECC Information");

                //quit excel object
                objCommon.QuitExcelApp(objExlApp, theWookbook, theWookSheet, null);
                System.GC.Collect();
                this.Close();
            }
            catch (Exception ex)
            {
                //quit excel object
                objCommon.QuitExcelApp(objExlApp, theWookbook, theWookSheet, null);
                System.GC.Collect();

                //MessageBox.Show(ex.ToString(), "ECC Catch Error");
                btnCreate.Enabled = true;
            }

        }

    }
}